﻿using System;
using System.Collections.Generic;
using System.Text;
using GFrame.Data;
using EZCRS.HCMS.SqlClient;

namespace EZCRS.HCMS.DataAcl
{
    public class ReportData
    {

        private SQLReader _sr = new SQLReader();

        public int GetReserveAmount(int Year, string Channel)
        {

            string strDate1 = Year.ToString() + "-1-1";

            int iNextYear = Year + 1;
            string strDate2 = iNextYear.ToString() + "-1-1";

            string strSQL = "SELECT SUM(sub.Rooms) FROM HCRS_ResvLog_Sub sub " +
                          " JOIN HCRS_ResvLog resv ON resv.VerifyCode = sub.VerifyCode " +
                          " WHERE sub.INDate >= @d1 AND sub.INDate < @d2 AND resv.Status <> 'cxl'";

            ParamList pl = new ParamList();

            pl.Add("@d1", System.Data.SqlDbType.DateTime, strDate1);
            pl.Add("@d2", System.Data.SqlDbType.DateTime, strDate2);



            if (!string.IsNullOrEmpty(Channel))
            {
                strSQL += " AND resv.Channel = @channel";

                pl.Add("@channel", System.Data.SqlDbType.VarChar, Channel);
            }

            object obj = _sr.ExecuteScalarCommand(strSQL, MainBase.CONNECTION_STRING, pl);


            if (obj != null && obj != DBNull.Value)
            {
                return Convert.ToInt32(obj);
            }

            return 0;
        }

        /// <summary>
        /// 房间数
        /// </summary>
        /// <param name="Year"></param>
        /// <param name="Month"></param>
        /// <param name="Channel"></param>
        /// <returns></returns>
        public int GetReserveMonthAmount(int Year, int Month, string Channel)
        {
            string strDate1 = Year.ToString() + "-" + Month.ToString() + "-1";

            if (Month >= 12)
                Month = 12;

            DateTime dt = Convert.ToDateTime(strDate1);
            
            while (dt.Month == Month)
            {
                dt = dt.AddDays(1);
            }

            string strSQL = "SELECT SUM(sub.Rooms) FROM HCRS_ResvLog_Sub sub " +
                          " JOIN HCRS_ResvLog resv ON resv.VerifyCode = sub.VerifyCode " +
                          " WHERE sub.INDate >= @d1 AND sub.INDate < @d2 AND resv.Status <> 'cxl'";

            ParamList pl = new ParamList();

            pl.Add("@d1", System.Data.SqlDbType.DateTime, strDate1);
            pl.Add("@d2", System.Data.SqlDbType.DateTime, dt);


            if (!string.IsNullOrEmpty(Channel))
            {
                strSQL += " AND resv.Channel = @channel";

                pl.Add("@channel", System.Data.SqlDbType.VarChar, Channel);
            }

            object obj = _sr.ExecuteScalarCommand(strSQL, MainBase.CONNECTION_STRING, pl);


            if (obj != null && obj != DBNull.Value)
            {
                return Convert.ToInt32(obj);
            }

            return 0;
        }

        /// <summary>
        /// 获取预订总数
        /// </summary>
        /// <param name="dt1"></param>
        /// <param name="dt2"></param>
        /// <param name="Channel"></param>
        /// <param name="Status"></param>
        /// <returns></returns>
        public int GetReservationCount(DateTime dt1, DateTime dt2, string Channel, string Status)
        {
            string strSQL = "SELECT COUNT(*) FROM HCRS_ResvLog_Sub sub " +
              " JOIN HCRS_ResvLog resv ON resv.VerifyCode = sub.VerifyCode " +
              " WHERE sub.INDate >= @d1 AND sub.INDate <= @d2 ";

            ParamList pl = new ParamList();

            pl.Add("@d1", System.Data.SqlDbType.DateTime, dt1);
            pl.Add("@d2", System.Data.SqlDbType.DateTime, dt2);

            if (!string.IsNullOrEmpty(Status))
            {
                strSQL += "AND resv.Status = @status";

                pl.Add("@status", System.Data.SqlDbType.VarChar, Status);
            }

            if (!string.IsNullOrEmpty(Channel))
            {
                strSQL += " AND resv.Channel = @channel";

                pl.Add("@channel", System.Data.SqlDbType.VarChar, Channel);
            }

            object obj = _sr.ExecuteScalarCommand(strSQL, MainBase.CONNECTION_STRING, pl);

            if (obj != null && obj != DBNull.Value)
            {
                return Convert.ToInt32(obj);
            }

            return 0;
        }


        public int GetReservationCount(DateTime dt1, DateTime dt2, string Channel, int Status)
        {
            string strSQL = "SELECT COUNT(*) FROM HCRS_ResvLog_Sub sub " +
              " JOIN HCRS_ResvLog resv ON resv.VerifyCode = sub.VerifyCode " +
              " WHERE sub.INDate >= @d1 AND sub.INDate <= @d2 ";

            ParamList pl = new ParamList();

            pl.Add("@d1", System.Data.SqlDbType.DateTime, dt1);
            pl.Add("@d2", System.Data.SqlDbType.DateTime, dt2);

            if (Status > 0)
            {
                strSQL += "AND sub.GetStatus = @status";

                pl.Add("@status", System.Data.SqlDbType.Int, Status);
            }

            if (!string.IsNullOrEmpty(Channel))
            {
                strSQL += " AND resv.Channel = @channel";

                pl.Add("@channel", System.Data.SqlDbType.VarChar, Channel);
            }

            object obj = _sr.ExecuteScalarCommand(strSQL, MainBase.CONNECTION_STRING, pl);

            if (obj != null && obj != DBNull.Value)
            {
                return Convert.ToInt32(obj);
            }

            return 0;
        }
    }
}
